/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Factory.DAO.MySQL;

import DatabaseConnection.DBConnectionFactory;
import Factory.DAO.TransactionDAO;
import Factory.DAOFactory;
import Factory.Tables.Transaction;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Patrick
 */
public class MySQLTransactionDAO extends TransactionDAO{

    @Override
    public void addTransaction(Transaction obj) {
        try {
            // TODO code application logic here
            DBConnectionFactory myFactory = DBConnectionFactory.getInstance(DAOFactory.MYSQL);
            try (Connection conn = myFactory.getConnection()) {
                PreparedStatement pstmt = conn.prepareStatement("insert into transaction(totalprice,datetransaction,timetransaction,iduser) values (?,?,?,?) ");
                pstmt.setFloat(1, obj.getTotalPrice());
                pstmt.setDate(2, obj.getTransactionDate());
                pstmt.setTime(3, obj.getTransactionTime());
                pstmt.setInt(4, obj.getUserID());
                pstmt.executeUpdate();
            }
        } catch (SQLException ex) {
            Logger.getLogger(MySQLTransactionDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    @Override
    public int getTransactionID(Transaction obj) {
        int transactionID = -1;
        try {
            // TODO code application logic here
            DBConnectionFactory myFactory = DBConnectionFactory.getInstance(DAOFactory.MYSQL);
            try (Connection conn = myFactory.getConnection()) {
                PreparedStatement pstmt = conn.prepareStatement("select idtransaction from transaction where iduser=? and cast(totalprice as decimal) = cast(? as decimal) and datetransaction=? and timetransaction=? ");
                pstmt.setInt(1, obj.getUserID());
                pstmt.setFloat(2, obj.getTotalPrice());
                pstmt.setDate(3, obj.getTransactionDate());
                pstmt.setTime(4, obj.getTransactionTime());
                ResultSet rs = pstmt.executeQuery();
                while(rs.next()){
                    transactionID = rs.getInt("idtransaction");
                }
            }
            return transactionID;
        } catch (SQLException ex) {
            Logger.getLogger(MySQLTransactionDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return transactionID;
    }

    @Override
    public List<Transaction> findTransactionByUserID(int userID) {
        try {
            // TODO code application logic here
            DBConnectionFactory myFactory = DBConnectionFactory.getInstance(DAOFactory.MYSQL);
            List<Transaction> result;
            try (Connection conn = myFactory.getConnection()) {
                PreparedStatement pstmt = conn.prepareStatement("select * from transaction where iduser=? order by datetransaction desc, timetransaction desc");
                pstmt.setInt(1, userID);
                ResultSet rs = pstmt.executeQuery();
                result = new ArrayList<>();
                Transaction tempTransaction;
                while(rs.next()){
                    tempTransaction = new Transaction();
                    tempTransaction.setTransactionID(rs.getInt("idtransaction"));
                    tempTransaction.setUserID(userID);
                    tempTransaction.setTotalPrice(rs.getFloat("totalPrice"));
                    tempTransaction.setTransactionDate(rs.getDate("datetransaction"));
                    tempTransaction.setTransactionTime(rs.getTime("timetransaction"));
                    
                    result.add(tempTransaction);
                }
            }
            return result;
        } catch (SQLException ex) {
            Logger.getLogger(MySQLTransactionDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    @Override
    public float getTotalSalesByCategory(int categoryID) {
        try {
            // TODO code application logic here
            DBConnectionFactory myFactory = DBConnectionFactory.getInstance(DAOFactory.MYSQL);
            float totalSales;
            try (Connection conn = myFactory.getConnection()) {
                PreparedStatement pstmt = conn.prepareStatement("select sum(totalprice) as 'TotalSales' from transaction natural join cart natural join movie natural join category where categoryid= ?");
                pstmt.setInt(1, categoryID);
                ResultSet rs = pstmt.executeQuery();
                totalSales = 0f;
                while(rs.next()){
                    totalSales = rs.getFloat("TotalSales");
                }
            }
            return totalSales;
        } catch (SQLException ex) {
            Logger.getLogger(MySQLTransactionDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return 0;
    }

    @Override
    public float getTotalSalesByMovie(int movieID) {
        try {
            // TODO code application logic here
            DBConnectionFactory myFactory = DBConnectionFactory.getInstance(DAOFactory.MYSQL);
            float totalSales;
            try (Connection conn = myFactory.getConnection()) {
                PreparedStatement pstmt = conn.prepareStatement("select sum(totalprice) as 'TotalSales' from transaction natural join cart natural join movie where idmovie= ?");
                pstmt.setInt(1, movieID);
                ResultSet rs = pstmt.executeQuery();
                totalSales = 0f;
                while(rs.next()){
                    totalSales = rs.getFloat("TotalSales");
                }
            }
            return totalSales;
        } catch (SQLException ex) {
            Logger.getLogger(MySQLTransactionDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return 0;
    }

    @Override
    public float getTotalSales() {
        try {
            // TODO code application logic here
            DBConnectionFactory myFactory = DBConnectionFactory.getInstance(DAOFactory.MYSQL);
            float totalSales;
            try (Connection conn = myFactory.getConnection()) {
                PreparedStatement pstmt = conn.prepareStatement("select sum(totalprice) as 'TotalSales' from transaction");
                ResultSet rs = pstmt.executeQuery();
                totalSales = 0f;
                while(rs.next()){
                    totalSales = rs.getFloat("TotalSales");
                }
            }
            return totalSales;
        } catch (SQLException ex) {
            Logger.getLogger(MySQLTransactionDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return 0;
    }
    
    
    
}
